﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Web.Mvc;
using Longtop.Web.SaleWeb.Admin.Excel;
using System.IO;
using Excel;
using System.Data;
using System.Web;
using Longtop.Web.Helpers;
using NHibernate.Linq;
using Longtop.Web.SaleWeb.Models;
using Longtop.Web.WebSite.Admin.Shared;
using OfficeOpenXml;
using NHibernate.Criterion;
using Longtop.Web.WebSite.Models;
using System.ComponentModel;
namespace Longtop.Web.WebSite.SaleWeb.Controllers {
    public class ExcelController : Admin.Controllers.ControllerBase {


        [Description("Excel数据验证")]
        [HttpPost]
        public ActionResult Create() {
            var files = Request.Files;
            var model = new CreateModel();
            if (files == null || files.Count == 0 || files[0].ContentLength == 0) ModelState.AddModelError("", "请选择一个excel文档");
            else {
                DataSet result = null;
                using (var stream = files[0].InputStream) {
                    ////1. Reading from a binary Excel file ('97-2003 format; *.xls)
                    //IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
                    //...
                    //2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
                    using (IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream)) {
                        //...
                        ////3. DataSet - The result of each spreadsheet will be created in the result.Tables
                        //DataSet result = excelReader.AsDataSet();
                        //...
                        //4. DataSet - Create column names from first row
                        excelReader.IsFirstRowAsColumnNames = true;
                        result = excelReader.AsDataSet();
                        excelReader.Close();
                    }
                }
                if (result.Tables.Count == 0) {
                    ModelState.AddModelError("", "未找到业绩数据行");
                    return View(model);
                }

                var table = result.Tables[0];

                FormCollection form = new FormCollection();
                var md = ModelMetadata.FromLambdaExpression<Achievement, Achievement>(c => c, new ViewDataDictionary<Achievement>());
                var template = md.Properties.Where(p => p.DisplayName != null).ToList();
                if (table.Columns.Count < template.Count) {
                    ModelState.AddModelError("", "该文档与服务器模板格式不匹配，重新下载模板，确保Excel数据表中列的名称和顺序与模板一致");
                }
                else {
                    foreach (DataRow item in table.Rows) {
                        for (int i = 0; i < template.Count; i++) {
                            if (template[i].PropertyName == "Sale")
                                form.Add("[" + table.Rows.IndexOf(item) + "].Sale.UserName", item[i].ToString());
                            else if (template[i].PropertyName == "Department")
                                form.Add("[" + table.Rows.IndexOf(item) + "].Department.Text", item[i].ToString());
                            else
                                form.Add("[" + table.Rows.IndexOf(item) + "]." + template[i].PropertyName, item[i].ToString());
                        }
                    }
                    return this.Import(form);
                }
            }
            return View(model);
        }
        [Description("Excel")]
        public ActionResult Index() {
            return View(new IndexMasterModel());
        }

        [ActionName("create")]
        [Description("导入Excel数据")]
        public ActionResult Import() {

            return View(new CreateModel());
        }

        [HttpPost]
        public ActionResult Import(FormCollection forms) {
            var model = new CreateModel();
            model.Model = new List<Achievement>(); var success = new List<Achievement>();
            try {
                TryUpdateModel(model.Model, forms);
                //var states = new KeyValuePair<string, ModelState>[ModelState.Count];
                //ModelState.CopyTo(states, 0);
                //ModelState.Clear();
                foreach (var item in model.Model) {
                    var i = model.Model.IndexOf(item);
                    bool isFalid = true;
                    FormCollection form = new FormCollection();
                    foreach (var it in forms.AllKeys) {
                        if (it.StartsWith("[" + i + "]"))
                            form.Add(it, forms[it]);
                    }

                    item.Department = Lable.FindOne(Expression.Eq("Text", item.Department.Text));
                    if (item.Department == null) {
                        ModelState.AddModelError(forms.AllKeys.First(p => p.StartsWith("[" + i + "]") && p.EndsWith("Department.Text")), "该部门不存在!");
                    }
                    else {
                        if (!Sale.Exists(Expression.Eq("UserName", item.Sale.UserName)) || !item.Department.Sales.Any(x => x.UserName == item.Sale.UserName)) {
                            ModelState.AddModelError(forms.AllKeys.First(p => p.StartsWith("[" + i + "]") && p.EndsWith("UserName")), "帐户不存在或不属于所选部门!");
                            if (!isFalid) model.fails++;
                        }
                    }
                    model.SuccessModel = success;


                    if (TryUpdateModel(item, form)) {
                        item.Create();
                        model.success++;
                        isFalid = false;
                        success.Add(item);
                    }
                    if (isFalid) model.fails++;
                }
                //foreach (var x in states) {
                //    if (ModelState.Keys.Contains(x.Key) == false)
                //        ModelState.Add(x);
                //}
                if (success.Count > 0) model.Model.RemoveAll(x => success.Contains(x));
                if (model.success > 0 || model.fails > 0)
                    model.OperateMessage = string.Format("操作完成!其中{0}{1}", model.success > 0 ? "成功导入数据[" + model.success + "]条" : "", model.fails > 0 ? "失败[" + model.fails + "]条,请修正后再次导入" : "");
                if (model.Model.Count == 0)
                    return View("create", model);
            }
            catch (Exception ex) {
                ModelState.AddModelError("", ex.Message);
            }
            model.SuccessModel = success;
            return View("import", model);
        }
        [Description("下载Excel模版")]
        public ActionResult Download() {
            FileInfo newFile = new FileInfo(Server.MapPath(@"~/content/achievement.xlsx"));
            if (!newFile.Exists) {
                using (ExcelPackage xlPackage = new ExcelPackage(newFile)) {
                    ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets.Add("Achievement1");
                    var m = ModelMetadata.FromLambdaExpression<Achievement, Achievement>(c => c, new ViewDataDictionary<Achievement>());
                    var i = 1;
                    foreach (var item in m.Properties) {
                        if (item.DisplayName != null) {
                            worksheet.Cell(1, i).Value = item.DisplayName;
                            worksheet.Cell(1, i).Style = "Good";
                            i++;
                        }
                    }
                    xlPackage.Save();
                }
            }
            return File(newFile.FullName, "Excel", "业绩数据模报.xlsx");
        }
    }
}
